# -*- coding:utf8 -*- #
# -----------------------------------------------------------------------------------
# ProjectName:  smalleel-api-tools
# FileName:     read_excel.py
# Author:
# Datetime:    2022/7/18 17:04
# Description：

# -----------------------------------------------------------------------------------

import openpyxl
import xlsxwriter


def read_excel(filename: str, sheetname: str = "Sheet1") -> list:
    """
    :param filename: 需要读取的excel文件名称
    :param sheetname: 默认为 sheet1
    :return: list
    """
    # 根据filename路径打开Excel文件
    workBook = openpyxl.load_workbook(filename, data_only=True)
    # 根据sheetname选择表格页进行操作
    allData = []
    ws = workBook[sheetname]
    # 行数据
    for rowData in ws:
        rowList = []
        for i in rowData:
            if i.value is None:
                i.value = ""
            rowList.append(i.value)
        allData.append(rowList)
    return allData


def write_excel(filename: str, data: str) -> None:
    workBook = xlsxwriter.Workbook(filename)  # 表名
    # 数据格式
    dataFormat = {
        'border': 1,
        'font_name': '黑体',  # 字体
        'font_size': 12,  # 字体大小
        'font_color': 'black',  # 字体颜色
        'align': 'center',  # 水平居中对齐
        'valign': 'vcenter'  # 垂直居中对齐
    }
    workSheet = workBook.add_worksheet(u'sheet1')
    workBook.add_format(dataFormat)
    # 二维数组多行写入
    cellValue1 = []
    cellValue2 = []
    for row_data in data:
        cellValue1.append(row_data[0])
        cellValue2.append(row_data[1])
    workSheet.write_column("A1", cellValue1)
    workSheet.write_column("B1", cellValue2)
    workBook.close()


# 调试代码入口
if __name__ == '__main__':
    # 打印文件内容
    print(read_excel(r"C:\Users\Administrator\Desktop\1111.xlsx"))
